#!/bin/sh
###################################################
###   基础表:      各地区计划招生人数统计
###   维护人:      ZhangWeiCe
###   数据源:

###  导入方式:      全量导入
###  运行命令:      sh major_enroll_area_count.sh &
###  结果目标:      model.major_enroll_area_count
###################################################

cd `dirname $0`
source ../../../config.sh
exec_dir major_enroll_area_count

HIVE_DB=model
HIVE_TABLE=major_enroll_area_count
TARGET_TABLE=major_enroll_area_count

function create_table(){
        hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
        hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
        hive -e "CREATE TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
                                        academy_code   STRING     COMMENT '系部代码',
                                        academy_name   STRING     COMMENT '系部名称',
                                        major_code   STRING     COMMENT '专业代码',
                                        major_name   STRING     COMMENT '专业名称',
                                        province_code   STRING     COMMENT '省代码',
                                        province_name   STRING     COMMENT '省名称',
                                        city_code   STRING     COMMENT '市代码',
                                        city_name   STRING     COMMENT '市名称',
                                        plan_single_student_count   STRING     COMMENT '计划单招人数',
                                        plan_ordinary_student_count   STRING     COMMENT '计划普招人数',
                                        actual_single_student_count   STRING     COMMENT '实际单招人数',
                                        semester_year   STRING     COMMENT '学年',
                                        actual_ordinary_student_count   STRING     COMMENT '实际普招人数'        )COMMENT  '各地区计划招生人数统计'
LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"

        fn_log "创建表--各地区计划招生人数统计: ${HIVE_DB}.${HIVE_TABLE}"
}

function import_table(){

        hive -e "insert overwrite table  ${HIVE_DB}.${HIVE_TABLE}

        select
            a.academy_code academy_code,
            a.academy_name academy_name,
            a.major_code major_code,
            nvl(a.major_name,'') major_name,
            a.province_code province_code,
            a.province_name province_name,
            '' city_code,
            '' city_name,
            cast(nvl(b.plan_single_student_count,0) as int) plan_single_student_count,
            cast(nvl(b.plan_ordinary_student_count,0) as int) plan_ordinary_student_count,
            cast(nvl(a.dzsl,0)as int) actual_single_student_count,
            a.semester_year semester_year,
            cast(nvl(a.tzsl,0) as int) actual_ordinary_student_count
        from (
                  SELECT
                    a.dwdm academy_code,
                    e.dwmc academy_name,
                    substr(a.zydm,1,6) major_code,
                    d.zymc major_name,
                    h.code province_code,
                    case when a.syddm like '%黑龙江%' then '黑龙江' when a.syddm like '%内蒙古%' then '内蒙古'
                        else  substring(a.syddm,1,2) end as  province_name,
                    sum(case when trim(g.lqxz)='单招' then 1 else 0 end) dzsl,
                    sum(case when trim(g.lqxz)='统招' then 1 else 0 end) tzsl,
                    concat(a.xznj,'-',cast(a.xznj as int)+1) semester_year
                FROM (select * from raw.sw_t_bzks a where a.xznj in ('2016','2017','2018') and a.xh not in (select xh from raw.sw_t_bzks where xznj='2016' and zydm='1701')) a
                left join (select zydm,zymc from raw.zgy_t_zg_zyxx group by zydm,zymc) d on a.zydm=substr(d.zydm,1,6)
                left join raw.pm_t_xx_dw e on a.dwdm=e.dwdm
                left join (select a.* from (select *,row_number() OVER (PARTITION BY xh ORDER BY xn desc) sort
                from raw.rs_t_zg_ksxx where sfbd='是') a where sort=1) g on a.xh=g.xh
                left join app.basic_area_info h on h.parent_id=0 and  substring(a.syddm,1,2)=substring(h.name,1,2)
                where h.code is not null
                group by a.dwdm,e.dwmc, substr(a.zydm,1,6),d.zymc,a.syddm,h.code,concat(a.xznj,'-',cast(a.xznj as int)+1)
        ) a left join(
                  SELECT
                    YBBH academy_code,
                    ZYBH major_code,
                    '610000' province_code,
                     sum(DDKSJH) plan_single_student_count,
                     sum(GKJH) plan_ordinary_student_count,
                     XN semester_year
                FROM raw.rs_t_zg_zsjhb
                group by YBBH,YBMC,ZYBH,ZYMC,xn
            ) b on a.semester_year=b.semester_year and a.academy_code=b.academy_code and a.major_code=b.major_code and a.province_code=b.province_code

        "
        fn_log " 导入数据--各地区计划招生人数统计: ${HIVE_DB}.${HIVE_TABLE}"

}

function export_table(){
    clear_mysql_data "TRUNCATE TABLE ${TARGET_TABLE};"

    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}  \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N'  \
    --columns "academy_code,academy_name,major_code,major_name,province_code,province_name,city_code,city_name,plan_single_student_count,plan_ordinary_student_count,actual_single_student_count,semester_year,actual_ordinary_student_count"

    fn_log "导出数据--各地区计划招生人数统计: ${HIVE_DB}.${TARGET_TABLE}"

}

init_exit
create_table
import_table
export_table
finish